package My

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"db2s/go-log/log"
	"db2s/parDef"
	"db2s/ref"
	"math"
	"reflect"
	"regexp"
	"strconv"
	"strings"
)

func isNumeric(s string) bool {
	// 正则表达式：^ 表示开始，$ 表示结束，\d 表示数字，\. 表示小数点
	re := regexp.MustCompile(`^[\d.]+$`)
	return re.MatchString(s)
}

func rowsLimitC(limit int) (p string) {
	if limit > 0 {
		p = fmt.Sprintf(" limit %v", limit)
	}
	return
}
func tableObject(schema, table, partition string) (p string) {
	p = fmt.Sprintf(" `%v`.`%v` T ", schema, table)
	if len(partition) > 0 && !strings.EqualFold(partition, "single") {
		p = fmt.Sprintf(" `%v`.`%v` PARTITION(%v) T ", schema, table, partition)
	}
	return
}
func whereSqlFilter(where string) (p string) {
	if len(where) > 0 {
		p = fmt.Sprintf(" and %v", where)
	}
	return
}
func whereAddFilter(where []map[string]string, col []parDef.ColMetaMapS) (p string) {
	var pp []string
	var null []string
	for _, v := range where {
		column, _ := v["columnName"]
		symbol, _ := v["symbol"]
		conditions, _ := v["conditions"]
		var subColType parDef.ColMetaMapS
		for _, colSub := range col {
			if strings.EqualFold(column, colSub.ColumnName) {
				subColType = colSub
				break
			}
		}
		switch {
		case strings.EqualFold(symbol, "is") && strings.EqualFold(conditions, "null"):
			null = append(null, fmt.Sprintf(" OR \"%v\" %v %v ", column, symbol, conditions))
		case strings.EqualFold(symbol, "is") && strings.EqualFold(conditions, "not null"):
			pp = append(pp, fmt.Sprintf(" AND \"%v\" %v %v ", column, symbol, conditions))
		default:
			switch subColType.TypeBelong {
			case "time":
				pp = append(pp, fmt.Sprintf(" AND \"%v\" %v %v ", column, symbol, newTimeValueToFunc(subColType.Type.ColTypeMap, conditions)))
			default:
				pp = append(pp, fmt.Sprintf(" AND \"%v\" %v %v ", column, symbol, conditions))
			}
		}
	}
	if len(pp) > 0 {
		pp = append(pp, null...)
	} else {
		return strings.ReplaceAll(strings.Join(null, " "), " OR ", " AND ")
	}
	return strings.Join(pp, " ")
}
func hitSpecify(s global.StartPart) (hitIndexName string) {
	//指定partition name会导致无法使用索引，进行全表扫描，且该sql为查询数据，已经通过索引列范围确定查询范围，所以不需要指定分区
	if len(s.SelectColumn.IndexName) > 0 {
		hitIndexName = fmt.Sprintf(" /*+ index(T %v) */ ", s.SelectColumn.IndexName)
	}
	return
}
func specifyScn(_ string) (p string) {
	return
}
func colAndWhere(s global.StartPart) (res colAndWhereResult, err error) {
	var (
		tableSql = TableSql{}
		event    = "[mysqlColAndWhere]"
	)
	if res.selectColumn, res.asSelectColumn, err = tableSql.SelectColumn(s.SelectColumn); err != nil || len(res.selectColumn) == 0 {
		err = errors.New(fmt.Sprintf("%v get sql select Column fail. Execution process:{chema:%v,table:%v,select column:%v,err:%v}", event, s.TableInfo.Schema, s.TableInfo.BackendTableName, s.SelectColumn, err))
		return
	}
	if res.whereCondition, err = tableSql.WhereGenerate(s.WhereGenerate); err != nil {
		err = errors.New(fmt.Sprintf("%v get sql select where Condition fail. Execution process:{chema:%v,table:%v,select where Condition:%v,err:%v}", event, s.TableInfo.Schema, s.TableInfo.BackendTableName, s.WhereGenerate, err))
		return
	}
	if res.orderByColumn, err = tableSql.OrderBY(s.WhereGenerate); err != nil {
		return
	}
	return
}
func removeQuotes(s string) string {
	// 定义正则表达式，匹配字符串开头和结尾的引号或反引号
	re := regexp.MustCompile(`^(["` + "`" + `]|["` + "`" + `])|(["` + "`" + `]|["` + "`" + `])$`)
	// 使用正则替换引号和反引号为空字符串
	return re.ReplaceAllString(s, "")
}
func columnNameRemoveQuotes(s []string) (res []string) {
	for _, v := range s {
		res = append(res, removeQuotes(v))
	}
	return
}

func nullEmpty(r string) (s string) {
	switch r {
	case "<null>":
	default:
		s = r
	}
	return
}
func stringNullActive(t any) (r any) {
	switch fmt.Sprintf("%v", t) {
	case "<null>", "<entry>":
		r = "NULL"
	default:
		r = fmt.Sprintf("%v", t)
	}
	return
}
func mapNullActive(s map[string]any) (p map[string]any) {
	p = make(map[string]any)
	for q, t := range s {
		switch fmt.Sprintf("%v", t) {
		case "<null>", "<entry>":
			p[q] = "NULL"
		default:
			p[q] = fmt.Sprintf("%v", t)
		}
	}
	return
}
func mapAnyToString(s map[string]any) (p map[string]string) {
	r := mapNullActive(s)
	p = make(map[string]string)
	for q, t := range r {
		switch fmt.Sprintf("%v", t) {
		case "<null>", "<entry>":
			p[q] = "NULL"
		default:
			p[q] = fmt.Sprintf("%v", t)
		}
	}
	return
}

func getExecSqlData(s any) (x execSqlS) {
	switch s.(type) {
	case global.TablesMetaInfoInput:
		p := s.(global.TablesMetaInfoInput)
		x = execSqlS{
			DB:    p.DB,
			Input: p.Input,
		}
	case global.SchemaObjectInput:
		p := s.(global.SchemaObjectInput)
		x = execSqlS{
			DB:    p.DB,
			Input: p.Input,
		}
	case global.GetColumnTypeInput:
		p := s.(global.GetColumnTypeInput)
		x = execSqlS{
			DB:    p.DB,
			Input: p.Input,
		}
	case global.TableSumInput:
		p := s.(global.TableSumInput)
		x = execSqlS{
			DB:    p.Db,
			Input: p.Input,
		}
	case global.RowsDataInput:
		p := s.(global.RowsDataInput)
		x = execSqlS{
			DB:    p.DB,
			Input: p.Input,
		}
	case global.DBGetCharacterS:
		p := s.(global.DBGetCharacterS)
		x = execSqlS{DB: p.DB, Input: p.Input}
	case global.StartPart:
		p := s.(global.StartPart)
		x = execSqlS{DB: p.DB, Input: p.Input}
	case global.TableActiveInput:
		p := s.(global.TableActiveInput)
		x = execSqlS{DB: p.DB, Input: p.Input}
	case global.DbParameterOptimizerS:
		p := s.(global.DbParameterOptimizerS)
		x = execSqlS{
			DB:        p.DB,
			Input:     p.Input,
			ShardName: p.ShardName,
		}
	case ConDbQuery:
		p := s.(ConDbQuery)
		x = execSqlS{
			DB:        map[string]*sql.DB{"single": p.DB},
			ShardName: p.ShardName,
			Input:     p.Input,
		}
	}
	return
}

func ExecSelectSql(e ExecSqlInputS) (timeout int64, res any, err error) {
	var (
		x               = getExecSqlData(e.Ss)
		expectTypeValue string
	)
	if len(e.ShardName) == 0 {
		e.ShardName = "single"
	}
	active := ETL.InstanceActiveS{DBType: DBType, DB: x.DB[e.ShardName], FixRows: x.Input.FixRows, StopTime: x.Input.SqlExecStopTime, SqlMode: x.Input.SqlMode}
	if len(x.DB) == 0 || active.DB == nil {
		err = errors.New(fmt.Sprintf("sql exec fail. Execution process:{sql:%v,shard:%v,db:%v,err:%v}", e.SelectSqlStr, e.ShardName, x.DB, err))
		return
	}
	if timeout, res, err = active.SqlQuery(e.SelectSqlStr, e.Expect); err != nil || res == nil {
		if err != nil {
			err = errors.New(fmt.Sprintf("sql exec fail. Execution process:{sql:%v,err:%v}", e.SelectSqlStr, err))
		}
		return
	}
	switch e.Expect {
	case ETL.Map:
		expectTypeValue = "map[string]interface {}"
	case ETL.StringGarth:
		expectTypeValue = "[]string"
	case ETL.String:
		expectTypeValue = "string"
	case ETL.StringPoint:
		expectTypeValue = "*string"
	case ETL.StringPointGarth:
		expectTypeValue = "[]*string"
	case ETL.GarthStringPointGarth:
		expectTypeValue = "[][]*string"
	case ETL.SMap:
		expectTypeValue = "[]map[string]interface {}"
	case ETL.FlowAny:
		expectTypeValue = "chan interface {}"
	case ETL.FlowString:
		expectTypeValue = "chan string"
	}
	if !strings.EqualFold(fmt.Sprintf("%v", reflect.TypeOf(res)), expectTypeValue) {
		err = errors.New(fmt.Sprintf("sql exec fail. result type mismatch! return:%v want:[]string.process:{sql:%v,result:%v}", reflect.TypeOf(res), e.SelectSqlStr, res))
		return
	}
	return
}
func newGetExecSqlData(s any) (x newExecSqlS, err error) {
	switch s.(type) {
	case Parameter:
		p := s.(Parameter)
		x = newExecSqlS{
			DB:      p.Con,
			options: p.Options,
		}
	case parDef.Parameter:
		p := s.(parDef.Parameter)
		x = newExecSqlS{
			DB: p.Con,
			options: Options{
				Scn:      p.Options.Scn,
				StopTime: p.Options.StopTime,
				WhereSql: p.Options.WhereSql,
				//WhereAdd:  p.Options.WhereAdd,
				SqlMode:   p.Options.SqlMode,
				SqlLogBin: p.Options.SqlLogBin,
			},
		}
	case ConDbQuery:
		p := s.(ConDbQuery)
		x = newExecSqlS{
			DB: p.DB,
		}
	default:
		err = ref.ErrAddPrintf("newGetExecSqlData", errors.New(fmt.Sprintf("type not match. curry type is %v", reflect.TypeOf(s))))
	}
	return
}
func newExecSelectSql(e ExecSqlInputS) (timeout int64, res any, err error) {
	var (
		x     newExecSqlS
		event = "[newExecSelectSql]"
	)
	if x, err = newGetExecSqlData(e.Ss); err != nil {
		err = ref.ErrAddPrintf(event, err)
		return
	}
	if len(e.ShardName) == 0 {
		e.ShardName = "single"
	}
	active := ETL.InstanceActiveS{DBType: DBType, DB: x.DB, FixRows: x.options.FixRows, GisValFunc: e.GisValFunc, StopTime: x.options.StopTime, SqlMode: x.options.SqlMode}
	if active.DB == nil {
		err = ref.ErrAddPrintf(event, errors.New(fmt.Sprintf("db connection is nil.")))
		return
	}
	if timeout, res, err = active.SqlQuery(e.SelectSqlStr, e.Expect); err != nil {
		err = ref.ErrAddPrintf(event, err)
		return
	}
	return
}
func ExecUpdateSql(e ExecSqlInputS) (timeout int64, err error) {
	var (
		x = getExecSqlData(e.Ss)
	)
	if len(e.ShardName) == 0 {
		e.ShardName = "single"
	}
	active := ETL.InstanceActiveS{DBType: DBType, DB: x.DB[e.ShardName], FixRows: x.Input.FixRows, StopTime: x.Input.SqlExecStopTime, SqlMode: x.Input.SqlMode}
	if len(x.DB) == 0 || active.DB == nil {
		err = errors.New(fmt.Sprintf("sql exec fail. Execution process:{sql:%v,shard:%v,db:%v,err:%v}", e.SelectSqlStr, e.ShardName, x.DB, err))
		return
	}
	if timeout, err = active.Update(e.UpdateSqlStr); err != nil {
		err = errors.New(fmt.Sprintf("sql exec fail. Execution process:{sql:%v,shard:%v,db:%v,err:%v}", e.SelectSqlStr, e.ShardName, x.DB, err))
		return
	}
	return
}
func newExecUpdateSql(event, schemaTable string, active ETL.WriteOpt, UpdateSqlStr []string) (execTime int64, err error) {
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	log.MainLog().DebugS(event, schemaTable, fmt.Sprintf(" exec sql is %v.", UpdateSqlStr))
	if active.DB == nil {
		err = fmt.Errorf("sql exec fail. connection point is nil")
		log.ErrorLog().ErrorS(event, schemaTable, fmt.Sprintf("sql exec fail. sql is %v connection point is nil", UpdateSqlStr))
		return
	}
	if execTime, err = ETL.Update(active, setConnectionSessionVariables(active.SqlMode, active.SqlLogBin), UpdateSqlStr); err != nil {
		log.ErrorLog().ErrorS(event, schemaTable, fmt.Sprintf("sql exec fail. exec sql is %v error is %v", UpdateSqlStr, err))
		return
	}
	log.MainLog().DebugS(event, schemaTable, fmt.Sprintf(" sql is %v, bath timeOut is %v ms.", UpdateSqlStr, execTime))
	return
}
func setConnectionSessionVariables(sqlMode, sqlLogBin bool) (res []string) {
	if sqlMode {
		res = append(res, SetSessionSqlModeSql("STRICT_TRANS_TABLES"), SetSessionSqlModeSql(""))
	}
	if sqlLogBin {
		res = append(res, SetSessionSqlLogBinOffSql("0"))
	}
	return
}
func newExecInsertSql(event string, active ETL.WriteOpt, writeSql any) (result global.Return, err error) {
	event = fmt.Sprintf("%v -> newExecInsertSql", event)
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	var res ETL.FnReturn
	var execWriteSql any
	switch writeSql.(type) {
	case map[string]any:
		active.LobSwitch = true
		for k, v := range writeSql.(map[string]any) {
			active.InsertPreFix = k
			execWriteSql = v
		}
	case [][]*string:
		execWriteSql = writeSql
	case any:
		execWriteSql = writeSql
	}
	if res = ETL.Insert(active, setConnectionSessionVariables(active.SqlMode, active.SqlLogBin), execWriteSql); res.Err != nil {
		err = ref.ErrAddPrintf(event, res.Err)
		return
	}
	log.MainLog().DebugS(event, fmt.Sprintf("sessionSql is %v insertSql is %v, bath timeOut is %v ms.", setConnectionSessionVariables(active.SqlMode, active.SqlLogBin), writeSql, res.ExecTime))
	result.Result = res.Result
	result.TimeOut = res.ExecTime
	return
}
func newExecDDLSql(event, schemaTable string, active ETL.WriteOpt, DDLSqlStr []string) (execTime int64, err error) {
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	log.MainLog().DebugS(event, schemaTable, fmt.Sprintf(" exec sql is %v.", DDLSqlStr))
	if active.DB == nil {
		err = fmt.Errorf("sql exec fail. connection point is nil")
		log.ErrorLog().ErrorS(event, schemaTable, fmt.Sprintf("sql exec fail. sql is %v connection point is nil", DDLSqlStr))
		return
	}
	if execTime, err = ETL.DDL(active, active.DB, setConnectionSessionVariables(active.SqlMode, active.SqlLogBin), DDLSqlStr); err != nil {
		log.ErrorLog().ErrorS(event, schemaTable, fmt.Sprintf("sql exec fail. exec sql is %v error is %v", DDLSqlStr, err))
		return
	}
	log.MainLog().DebugS(event, schemaTable, fmt.Sprintf(" sql is %v, bath timeOut is %v ms.", DDLSqlStr, execTime))
	return
}

func QueryColValManage(val any) (newVal any) {
	if val == nil || len(fmt.Sprintf("%v", val)) == 0 || strings.EqualFold(fmt.Sprintf("'%v'", val), "'<nil>'") {
		switch {
		case len(fmt.Sprintf("%v", val)) == 0:
			newVal = "<entry>" //空字符串
		case fmt.Sprintf("%v", val) == "<nil>":
			newVal = "<null>" //null
		}
		return
	} else {
		newVal = val
	}
	switch val.(type) {
	case string:
		if val == nil {
			return
		}
		switch {
		case strings.Contains(val.(string), "SRID:") && strings.Contains(val.(string), "AXIS:OGC"): //wkt模式
			var err error
			if newVal, err = GeomWktToJson(val); err != nil {
				return err
			}
		default:
			return newVal
		}
	default:
		return newVal
	}
	return
}
func forExecQuerySql(event string, parameter any, Expect string, execSql []string) (result global.Return, err error) {
	defer func() {
		if err != nil {
			err = ref.ErrAddPrintf("forExecQuerySql", err)
		}
	}()
	for _, v := range execSql {
		result.Sql = v
		if result.TimeOut, result.Result, err = newExecSelectSql(ExecSqlInputS{
			Ss:           parameter,
			Event:        event,
			SelectSqlStr: result.Sql,
			Expect:       Expect,
			GisValFunc:   QueryColValManage,
		}); err != nil || result.Result == nil {
			continue
		} else {
			return
		}
	}
	return
}

func stringAnyToInt64(s any) (res int64, err error) {
	switch pp := stringNullActive(s); pp.(type) {
	case string:
		if !strings.EqualFold(pp.(string), "NULL") {
			return strconv.ParseInt(fmt.Sprintf("%v", pp), 10, 64)
		}
	}
	return
}

// CalculateDecimalSize 计算 DECIMAL(M, D) 的存储大小
func CalculateDecimalSize(M int64) int {
	if M < 0 {
		return 0 // M 不能为负
	}
	// 使用公式: FLOOR((M + 1) / 2) + 1
	return int(math.Floor(float64(M+1)/2)) + 1
}

func columnInvisibleOptions(invisible bool) (ch string) {
	if invisible {
		return " /*!80023 INVISIBLE */"
	}
	return
}
func columnAutoIncrementOptions(autoIncrement bool) (ch string) {
	if autoIncrement {
		return "auto_increment"
	}
	return
}
func createTableOptions(r []string, rr string) bool {
	for _, v := range r {
		if strings.EqualFold(v, rr) {
			return true
		}
	}
	return false
}
func generalChartOptions(a global.TableMeta, r []string) (ch string) {
	if createTableOptions(r, "chart") {
		ch = nullEmpty(a.Charset)
		if len(ch) > 0 {
			ch = fmt.Sprintf(" CHARACTER SET %v ", ch)
		}
	}
	return
}
func generalComment(prefix, s string) string {
	var replicationStr = []string{"'", ";"}
	switch {
	case s == "<entry>", s == "<null>", s == "''", s == "NULL":
		return fmt.Sprintf("%v ''", prefix)
	case len(s) == 0:
		return ""
	case func() bool {
		var e bool
		for _, v := range replicationStr {
			if strings.Contains(s, v) {
				e = true
			}
		}
		return e
	}():
		var q = s
		for _, v := range replicationStr {
			if strings.Contains(s, v) {
				q = strings.ReplaceAll(q, fmt.Sprintf("%v", v), fmt.Sprintf("\\%v", v))
			}
		}
		return fmt.Sprintf("%v '%v'", prefix, q)
	default:
		return fmt.Sprintf("%v '%v'", prefix, s)
	}
}
func metaBaseInit(ctx context.Context) (result MetaBaseInitResult, err error) {
	var meta = parDef.GetFuncParameterValue(ctx, parDef.GetMetaParameterValue())
	result = MetaBaseInitResult{}
	switch meta.(map[string]any)["columnMe"].(type) {
	case []parDef.ColumnMeta:
		result.columnMeta = meta.(map[string]any)["columnMe"].([]parDef.ColumnMeta)
	}
	switch meta.(map[string]any)["constraintsMe"].(type) {
	case []parDef.Constraints:
		result.constraintsMeta = meta.(map[string]any)["constraintsMe"].([]parDef.Constraints)
	}
	switch meta.(map[string]any)["columnMetaMap"].(type) {
	case []parDef.ColMetaMapS:
		result.columnMetaMap = meta.(map[string]any)["columnMetaMap"].([]parDef.ColMetaMapS)
	}
	switch meta.(map[string]any)["indexMe"].(type) {
	case []parDef.IndexColumnMe:
		result.indexMeta = meta.(map[string]any)["indexMe"].([]parDef.IndexColumnMe)
	}
	switch meta.(map[string]any)["partMe"].(type) {
	case parDef.PartMetaData:
		result.partMe = meta.(map[string]any)["partMe"].(parDef.PartMetaData)
	}
	switch meta.(map[string]any)["commentMe"].(type) {
	case string:
		result.Comment = meta.(map[string]any)["commentMe"].(string)
	}
	return
}

func getTableName(schema, table any) (tableName string) {
	return fmt.Sprintf(" %v.%v ", schema, table)
}
func getLikeTable(table string) (res string) {
	switch {
	case table == "*":
		return ""
	case strings.Contains(table, "%"):
		return fmt.Sprintf("and table_name like '%v'", table)
	default:
		return fmt.Sprintf("and table_name = '%v'", table)
	}
}
func getLikeSeqName(table string) (res string) {
	switch {
	case table == "*":
		return ""
	case strings.Contains(table, "%"):
		return fmt.Sprintf("and NAME like '%v'", table)
	default:
		return fmt.Sprintf("and NAME = '%v'", table)
	}
}
func getSysObjectView(p ...string) (sysView []string) {
	return p
}
func generalQuerySysObjectSql(s, t string, o []string, body func(o, s, t string) string) (res []string) {
	for _, v := range o {
		res = append(res, body(v, s, t))
	}
	return
}
func ctxDoneExecFunc(ctx context.Context, exec func() (global.Return, error)) (result global.Return, err error) {
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if exec != nil {
			return exec()
		}
		return
	}
}
